package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 封装查询和新增，修改，删除数据
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsername(String username) {
        String sql = "select * from user where username='" + username + "'";
        ResultSet query = JDBC.query(sql);
        UserModel userModel = null;
        try {
//            根据查询出的结果去遍历query的数据
//            如果有数据，则进入while循环
//            ，如果没有，则直接返回null
            while (query.next()) {
                userModel = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userModel;
    }

    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` " +
                "( `username`, `password`, " +
                "`phone`, `sex`, `birthday`, " +
                "`hobby`, `introduce`, `remark`," +
                " `update_time`, `create_time`, " +
                "`enable`, `email`, `height`, " +
                "`address`) VALUES " +
                "('" + userModel.getUsername() + "', '" + userModel.getPassword() + "', " +
                "'" + userModel.getPhone() + "', '" + userModel.getSex() + "', '" + userModel.getBirthday() + "'," +
                " '" + userModel.getHobby() + "', '', ''," +
                " now(), sysdate(), " +
                "'0', '" + userModel.getEmail() + "', '0'," +
                " '');";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel model) {
        StringBuilder sql = new StringBuilder("select * from user where true ");
        if (!"".equals(model.getUsername())) {
            sql.append(" and username like '%").append(model.getUsername()).append("%' ");
        }
        if (!"".equals(model.getPhone())) {
            sql.append(" and phone like '%").append(model.getPhone()).append("%' ");
        }
        if (!"全部".equals(model.getSex())) {
            sql.append(" and sex ='").append(model.getSex()).append("' ");
        }

        if (!"".equals(model.getStartBirthday()) && "".equals(model.getEndBirthday())) {
            sql.append(" and birthday>='").append(model.getStartBirthday()).append("' ");
        }
        if ("".equals(model.getStartBirthday()) && !"".equals(model.getEndBirthday())) {
            sql.append(" and birthday<='").append(model.getEndBirthday()).append("' ");
        }
        if (!"".equals(model.getStartBirthday()) && !"".equals(model.getEndBirthday())) {
            sql.append(" and birthday between '").append(model.getStartBirthday()).append("' and '").append(model.getEndBirthday()).append("' ");
        }
//        if (sql.startsWith("and")) {
//            substring（a,b）截取字符串 从a开始截取，到b结束
//            sql = sql.substring(4, sql.length());
//        }
//        hobby可能为多个爱好，所以需要分开查询
        if (model.getHobby() != null && !"".equals(model.getHobby())) {
            String[] hobbys = model.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql.append(" and find_in_set('").append(hobbys[i]).append("',hobby)");
            }
        }

        sql.append(" order by id desc ");
//        分页,curPage第几页。pageSize每一页显示的条数
        sql.append(" limit  " + (model.getPageModel().getCurPage() - 1) * model.getPageModel().getPageSize() + "," + model.getPageModel().getPageSize() + "");
        return setData(sql.toString());
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select * from user where id=" + userModel.getId();
        return setData(sql).get(0);
    }

    private List<UserModel> setData(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel userModel = new UserModel();
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPhone(resultSet.getString("phone"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setBirthday(resultSet.getString("birthday"));
                userModel.setEmail(resultSet.getString("email"));
                userModel.setEnable(resultSet.getInt("enable"));
                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (list.size() == 0) {
            list.add(null);
        }
        return list;
    }

    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET ";
        if (!userModel.getUsername().equals("") && userModel.getUsername() != null) {
            sql += "`username` = '" + userModel.getUsername() + "', ";
        }
        if (!userModel.getPhone().equals("") && userModel.getPhone() != null) {
            sql += "`phone` = '" + userModel.getPhone() + "', ";
        }
        if (!userModel.getSex().equals("") && userModel.getSex() != null) {
            sql += "`sex` = '" + userModel.getSex() + "', ";
        }

        if (userModel.getBirthday() == null) {
            sql += "`birthday` = now(), ";
        } else {
            sql += "`birthday` = '" + userModel.getBirthday() + "', ";
        }
        if (userModel.getHobby() != null) {
            sql += "`hobby` = '" + userModel.getHobby() + "', ";
        }

        sql += "`update_time` = now(), ";
        sql += "`enable` = '0', " +
                "`email` = '" + userModel.getEmail() + "' " +
                "WHERE (`id` = '" + userModel.getId() + "');\n";
        return JDBC.update(sql);

    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int updateEnable(UserModel userModel) {

        String sql = "update user " +
                " set " +
                " enable  = abs(enable-1) " +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel model) {
        StringBuilder sql = new StringBuilder("select count(*) count  from user where true ");
        if (!"".equals(model.getUsername())) {
            sql.append(" and username like '%").append(model.getUsername()).append("%' ");
        }
        if (!"".equals(model.getPhone())) {
            sql.append(" and phone like '%").append(model.getPhone()).append("%' ");
        }
        if (!"全部".equals(model.getSex())) {
            sql.append(" and sex ='").append(model.getSex()).append("' ");
        }

        if (!"".equals(model.getStartBirthday()) && "".equals(model.getEndBirthday())) {
            sql.append(" and birthday>='").append(model.getStartBirthday()).append("' ");
        }
        if ("".equals(model.getStartBirthday()) && !"".equals(model.getEndBirthday())) {
            sql.append(" and birthday<='").append(model.getEndBirthday()).append("' ");
        }
        if (!"".equals(model.getStartBirthday()) && !"".equals(model.getEndBirthday())) {
            sql.append(" and birthday between '").append(model.getStartBirthday()).append("' and '").append(model.getEndBirthday()).append("' ");
        }
//        if (sql.startsWith("and")) {
//            substring（a,b）截取字符串 从a开始截取，到b结束
//            sql = sql.substring(4, sql.length());
//        }
//        hobby可能为多个爱好，所以需要分开查询
        if (model.getHobby() != null && !"".equals(model.getHobby())) {
            String[] hobbys = model.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql.append(" and find_in_set('").append(hobbys[i]).append("',hobby)");
            }
        }

        sql.append(" order by id desc ");

        int count = 0;
        ResultSet resultSet = JDBC.query(sql.toString());
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }
}
